﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DBHelper
{
    /// <summary>
    /// 数据库访问封装类
    /// </summary>
    public class SQLHepler
    { 
        /// <summary>
        /// 连接字符串
        /// </summary>
        //public static string connStr = ConfigurationManager.AppSettings["connectionStr"].ToString();
        public static string connStr = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;

        /// <summary>
        /// 执行分页查询
        /// </summary>
        /// <param name="orderby">排序语句</param>
        /// <param name="tableName">表名称</param>
        /// <param name="sqlWhere">查询条件</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <returns>DataSet</returns>
        public static DataSet GetPagingDataSet(string orderby, string tableName, string sqlWhere, int pageIndex, int pageSize)
        {
            StringBuilder sqlText = new StringBuilder();
            sqlText.AppendFormat(" select * from (select ROW_NUMBER() over(order by {0} ) as rowid,* from {1} ", orderby, tableName);
            sqlText.AppendFormat("	where 1=1 {0} ", sqlWhere);
            sqlText.AppendFormat(" ) newTable ");
            sqlText.AppendFormat(" where rowid between ((({0} - 1) * {1}) + 1)  and  ({0} * {1}) ", pageIndex, pageSize);
            sqlText.AppendFormat(" select count(1) as RecordCount from {0}  where 1=1 {1} ", tableName, sqlWhere);

            SqlCommand comm = null;
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connStr);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                comm = new SqlCommand(sqlText.ToString(), conn);
                SqlDataAdapter sda = new SqlDataAdapter(comm);
                ds = new DataSet();
                sda.Fill(ds);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, comm, conn);
            }
            return ds;
        }

        #region 外部调用

        /// <summary>
        /// 执行sql语句返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteSql(string sql, params SqlParameter[] param)
        {
            SqlCommand comm = null;
            SqlConnection conn = null;
            int count = 0;
            try
            {
                conn = new SqlConnection(connStr);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                comm = new SqlCommand(sql, conn);
                comm.Parameters.AddRange(param);
                count = comm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, comm, conn);
            }
            return count;
        }

        /// <summary>
        /// 执行sql语句，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] param)
        {
            SqlCommand comm = null;
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connStr);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                comm = new SqlCommand(sql, conn);
                comm.Parameters.AddRange(param);
                SqlDataAdapter sda = new SqlDataAdapter(comm);
                ds = new DataSet();
                sda.Fill(ds);
                sda.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, comm, conn);
            }
            return ds.Tables[0];
        }
        #endregion

        /// <summary>
        /// 关闭对象，释放资源
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        protected static void CloseDispose(SqlDataReader dr, SqlCommand cmd, SqlConnection con)
        {
            if (dr != null)
            {
                dr.Close();
                dr.Dispose();
            }
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
        }
    }
}
